package com.example.yingjiguanli_demo.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.yingjiguanli_demo.pojo.ApplicationWithMentorDTO;
import com.example.yingjiguanli_demo.pojo.ApplicationWithStudentAndMentorDTO;
import com.example.yingjiguanli_demo.pojo.Applications;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 学生申请导师表 Mapper 接口
 * </p>
 *
 * @author scj
 * @since 2024-12-07
 */
@Mapper
public interface ApplicationsMapper extends BaseMapper<Applications> {
    // 根据 studentId 查询所有申请，并返回学生和导师信息
    @Select("SELECT a.id AS applicationId, a.student_id AS studentId, a.mentor_id AS mentorId, " +
            "a.application_reason AS applicationReason, a.research_interests AS researchInterests, " +
            "a.status AS status, a.created_at AS createdAt, " +
            "m.mentor_name " +
            "FROM applications a " +
            "JOIN mentor_profiles m ON a.mentor_id = m.mentor_id " +
            "WHERE a.student_id = #{studentId}")
    List<ApplicationWithMentorDTO> getApplicationsWithMentorByStudentId(Integer studentId);

    /**
     * 根据 mentorId 查询申请信息，并分页，返回包含学生和导师信息的结果
     * @param page 分页对象
     * @param mentorId 导师ID
     * @return IPage<ApplicationWithStudentAndMentorDTO>
     */
    @Select("SELECT a.id, " +
            "a.student_id AS studentId, " +
            "s.username AS studentName, " +
            "a.mentor_id AS mentorId, " +
            "mp.mentor_name AS mentorProfileName, " +
            "a.application_reason AS applicationReason, " +
            "a.research_interests AS researchInterests, " +
            "a.status, " +
            "a.created_at AS createdAt " +
            "FROM applications a " +
            "JOIN users s ON a.student_id = s.id " +  // Join student info
            "JOIN users m ON a.mentor_id = m.id " +    // Join mentor info
            "JOIN mentor_profiles mp ON a.mentor_id = mp.mentor_id " +  // Join mentor profiles for detailed info
            "WHERE a.mentor_id = #{mentorId} " +      // 根据 mentorId 过滤
            "ORDER BY a.created_at DESC")
    IPage<ApplicationWithStudentAndMentorDTO> selectApplicationsWithStudentAndMentorPage(
            @Param("page") Page<?> page,
            @Param("mentorId") Long mentorId
    );

}
